/*
 * Copyright (C), 2002-2016, 你的兔宝宝电子商务有限公司
 * FileName: ExcelBase.java
 * Author:   你的兔宝宝
 * Date:     2016年7月19日 上午9:01:34
 * Description: //模块目的、功能描述      
 * History: //修改记录
 * <author>      <time>      <version>    <desc>
 * 修改人姓名             修改时间            版本号                  描述
 */
package com.baby.demo.utils;

/**
 * 〈一句话功能简述〉<br> 
 * 〈功能详细描述〉
 *
 * @author 你的兔宝宝
 * @see [相关类/方法]（可选）
 * @since [产品/模块版本] （可选）
 */
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;



public class ExcelBs {
    
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelBs.class);
    
    public void k() {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(); // 产生工作簿对象
            Sheet sheet = workbook.createSheet(); // 产生工作表对象
            // 设置第一个工作表的名称为firstSheet
            // 为了工作表能支持中文，设置字符编码为UTF_16
            workbook.setSheetName(0, "firstSheet");
            // 产生一行
            Row row = sheet.createRow((short) 0);
            // 产生第一个单元格
            Cell cell = row.createCell((short) 0);
            // 设置单元格内容为字符串型
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 为了能在单元格中写入中文，设置字符编码为UTF_16。
            // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
            // 往第一个单元格中写入信息
            cell.setCellValue("测试成功");
            String xlsFile = "";
            FileOutputStream fOut = new FileOutputStream(xlsFile);
            workbook.write(fOut);
            fOut.flush();
            fOut.close();
            System.out.println("文件生成...");

            // 以下语句读取生成的Excel文件内容
            FileInputStream fIn = new FileInputStream(xlsFile);
            HSSFWorkbook readWorkBook = new HSSFWorkbook(fIn);
            Sheet readSheet = readWorkBook.getSheet("firstSheet");
            Row readRow = readSheet.getRow(0);
            Cell readCell = readRow.getCell((short) 0);
            System.out.println("第一个单元是：" + readCell.getStringCellValue());
        } catch (Exception e) {
            System.out.println(e);
        }

    }
    
    
    /**
     * 获取单元格的值
     * @param hssfCell
     * @return
     */
    static String getValue(Cell hssfCell) {
        if (hssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型的值
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值 
            double num = hssfCell.getNumericCellValue();
            boolean isDate = HSSFDateUtil.isCellDateFormatted(hssfCell);
            isDate = isDate && HSSFDateUtil.isCellInternalDateFormatted(hssfCell);
            if(isDate && HSSFDateUtil.isValidExcelDate(num)){
                return Strings.format(HSSFDateUtil.getJavaDate(num), "yyyy-MM-dd hh:mm:ss");
            }else
                return Strings.parseNum(String.valueOf(num));//getRichStringCellValue
        } else {
            // 返回字符串类型的值
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    
    /**
     * 将row转为map
     * @param row
     * @param offsetCol
     * @param fields
     * @return
     */
    static Map<String,Object> readRow4map(Row row,int offsetCol,String[] fields){
        int colnum = Math.min(row.getLastCellNum(), fields.length);
        Map<String,Object> obj = new HashMap<String,Object>();
        boolean empty = true;
        for (int j = offsetCol,i = 0; j <= colnum; j++,i++) {
            Cell cell = row.getCell(j);
            if(cell == null ) continue;
            String value = getValue(cell);
            String field = fields[i];
            if( Strings.isEmpty(field) ) continue;
            empty = empty && Strings.isEmpty(value);
            obj.put(field, Strings.show(value));
        }
        if( empty ){
            obj = new HashMap<String,Object>();
        }
        return obj;
    }
    
    
    /**
     * 读取excel
     * @param fis   输入流
     * @param pfields 字段
     * @param offsetCol 偏移列:从0开始
     * @param offsetRow 偏移行:从0开始
     * @param offsetSheet 偏移表格地址:从0开始
     * @param offsetSheetName 偏移表格名称
     * @param headerRow 头行:从0开始
     * @param clazz 装备的数据类
     * @param table
     * @return List
     */
    public static List<Map<String,Object>> read4xls(FileInputStream fis, String[] pfields,int offsetCol,int offsetRow,int offsetSheet,String offsetSheetName,int headerRow){
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        Workbook xwb = null;
        Sheet sheet;
        try {
            xwb = new HSSFWorkbook(fis); //OPCPackage
        } catch (Exception e) {
            LOGGER.error(e.getMessage(),e);
            return list;
        }
        int sheet_nums = xwb.getNumberOfSheets();
        if( offsetSheet >= 0 ){
            sheet =  xwb.getSheetAt(offsetSheet);//sxwb.getSheetAt(0);
        }else{
            sheet =  xwb.getSheet(offsetSheetName);
        }

        //自动定位数据位置
        if( offsetCol < 0 || offsetRow < 0){
            int[] pos = locate(sheet);
            offsetCol = pos[1];
            offsetRow = pos[0] + 1;
            if( pfields == null ){
                headerRow = pos[0];
            }
        }
        //字段映射处理
        String[] fields = pfields;
        if( headerRow >=0 ){
            List<String> ofields = new ArrayList<String>();
            Row row0 = sheet.getRow(headerRow);
            int colnum = row0.getLastCellNum();
            fields = new String[colnum];
            for (int j = offsetCol; j < colnum; j++) {
                Cell cell = row0.getCell(j);
                if(cell != null) {
                    String field = getValue(cell);
                    if( !Strings.isEmpty(field) ) {
                        field = field.trim();
                        ofields.add(field);
                    }
                }
            }
            if( !Collections.isEmpty(ofields) ){
                fields = new String[ofields.size()];
                fields = ofields.toArray( fields );
            }
        }
        
        int rownum = sheet.getLastRowNum();
        for (int i = offsetRow; i <= rownum; i++) {
            Row row = sheet.getRow(i);
            if(row == null) continue;
            Map<String, Object> obj = readRow4map(row,offsetCol,fields);
            if( !Collections.isEmpty(obj) ){
                list.add(obj);
            }
        }
        LOGGER.debug("total sheets:    " + sheet_nums + "  total rows: " + rownum);
        return list;
    }
    
    /**
     * 读取excel
     * @param fis   输入流
     * @param pfields 字段
     * @param offsetCol 偏移列:从0开始
     * @param offsetRow 偏移行:从0开始
     * @param offsetSheet 偏移表格地址:从0开始
     * @param offsetSheetName 偏移表格名称
     * @param headerRow 头行:从0开始
     * @param clazz 装备的数据类
     * @param table
     * @return List
     */
    public static List<Map<String,Object>> read4xlsx(FileInputStream fis, String[] pfields,int offsetCol,int offsetRow,int offsetSheet,String offsetSheetName,int headerRow){
        System.setProperty("org.xml.sax.driver","org.apache.xerces.parsers.SAXParser");
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        Workbook xwb = null;
        Sheet sheet;
        try {
            xwb = new XSSFWorkbook(fis);//POIFSFileSystem
        } catch (Exception e) {
            LOGGER.error(e.getMessage(),e);
            return list;
        }
        int sheet_nums = xwb.getNumberOfSheets();
        if(offsetSheet >= 0){
            sheet =  xwb.getSheetAt(offsetSheet);//sxwb.getSheetAt(0);
        }else{
            sheet =  xwb.getSheet(offsetSheetName);
        }
        //自定定位数据位置
        if( offsetCol < 0 || offsetRow < 0){
            int[] pos = locate(sheet);
            offsetCol = pos[1];
            offsetRow = pos[0] + 1;
            if( pfields == null ){
                headerRow = pos[0];
            }
        }
        //映射字段处理
        String[] fields = pfields;
        if( headerRow >=0 ){
            List<String> ofields = new ArrayList<String>();
            Row row0 = sheet.getRow(headerRow);
            int colnum = row0.getLastCellNum();
            fields = new String[colnum];
            for (int j = offsetCol; j < colnum; j++) {
                Cell cell = row0.getCell(j);
                if(cell != null) {
                    String field = getValue(cell);
                    if( !Strings.isEmpty(field) ) {
                        field = field.trim();
                        ofields.add(field);
                    }
                }
            }
            if( !Collections.isEmpty(ofields) ){
                fields = new String[ofields.size()];
                fields = ofields.toArray( fields );
            }
        }
        
        int rownum = sheet.getLastRowNum();
        for (int i = offsetRow; i <= rownum; i++) {
            Row row = sheet.getRow(i);
            if(row == null) continue;
            Map<String, Object> obj = readRow4map(row,offsetCol,fields);
            if( !Collections.isEmpty(obj) ){
                list.add(obj);
            }
        }
        LOGGER.debug("total sheets:    " + sheet_nums + "  total rows: " + rownum);
        return list;
    }
    
    public static int[] locate(Sheet sheet){
        int rownum = sheet.getLastRowNum(),colat = -1, rowat = -1;
        for (int i = 0; i <= rownum; i++) {
            Row row = sheet.getRow(i);
            if( row == null ){
                continue;
            }
            int colnum = row.getLastCellNum();
            for (int j = 0; j < colnum; j++) {
                Cell cell = row.getCell(j);
                if(cell != null) {
                    String value = getValue(cell);
                    if( !Strings.isEmpty(value) ){
                        colat = j;
                        break;
                    }
                };
            }
            if( colat >= 0){
                rowat = i;
                break;
            }
        }
        return new int[]{rowat,colat};
    }
    
    /**
     * 
     * @param file 文件
     * @param pfields 字段
     * @param offsetCol 偏移列:从0开始
     * @param offsetRow 偏移行:从0开始
     * @param offsetSheet 偏移表格地址:从0开始
     * @param offsetSheetName 偏移表格名称
     * @param headerRow 头行:从0开始
     * @param clazz 装备的数据类
     * @param table
     * @return
     */
    public static List<Map<String,Object>> read4map(String file, String[] pfields,int offsetCol,int offsetRow,int offsetSheet,String offsetSheetName,int headerRow){
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            LOGGER.error(e.getMessage());
        }
        if(file.toLowerCase().endsWith("x")){
            return read4xlsx(fis,pfields,offsetCol,offsetRow,offsetSheet,offsetSheetName,headerRow);
        }
        return read4xls(fis,pfields,offsetCol,offsetRow,offsetSheet,offsetSheetName,headerRow);
    }
    
    public static List<Map<String,Object>> read4map(String file, String[] pfields){
        return read4map(file, pfields,-1,-1,0,null,-1);
    }
    
    
    public static void main(String[] args) {
        String file = "H:\\Documents and Settings\\Administrator\\桌面\\需求\\外网需求——数据江苏省环评机构信息.xlsx";
        String[] fileds = new String[]{"oid","area","city","name","qNum","aRange","bRange","range","odate","basicInfo","rap"};
        String[] fieldDesc = new String[]{"序号",     "所在地","地级市",    "机构名称", "机构资质证号",   "甲级报告书评价范围",    "乙级报告书评价范围",    "报告表评价范围",  "有效期",  "基本情况", "奖 惩",};
        List<Map<String,Object>> list = read4map(file,fileds,0,2,0,null,-1);
        
        
        
        String header = "";
        for (int i = 0; i < fieldDesc.length; i ++) {
            String key = fieldDesc[i];
            header += key + "   ";
        }
        LOGGER.info(header);
        
        for (int i = 0; i < list.size(); i++) {
            Map<String,Object> obj = list.get(i);
            String values = "";
            for (int j = 0; j < fileds.length; j ++) {
                String key = fileds[j];
                Object value = obj.get(key);
                values += value + " ";
            }
            LOGGER.info(values);
        }
        LOGGER.info("total data:   " + list.size());
    }
    
}
